import datetime
import re
import time

import redis
import requests
from lxml import etree

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Text, ForeignKey, DateTime, Enum, Boolean
from sqlalchemy.orm import sessionmaker

from random import randint

# 链接是需要指定要用到的MySQL数据库
engine = create_engine('mysql+pymysql://root:python@localhost:3306/caipu?charset=utf8mb4', echo=True)
Base = declarative_base()  # 生成SQLORM基类


class CP_Foods(Base):
    """菜品"""
    __tablename__ = "cp_foods"

    id = Column(Integer, primary_key=True)
    name = Column(String(256), nullable=False)
    title = Column(String(256), nullable=False)  # 帖子标题
    default_image = Column(String(256), nullable=False)  #
    user_id = Column(Integer, default=1)
    create_time = Column(DateTime, default=datetime.datetime.utcnow)  # 记录的创建时间
    update_time = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)  # 记录的更新时间


class FoodCate(Base):
    """菜品分类"""
    __tablename__ = "cp_food_category"
    id = Column(Integer, primary_key=True)  # 分类编号
    category_id = Column(Integer, default=4)
    food_id = Column(Integer, ForeignKey("cp_foods.id"))


class DoSing(Base):
    """菜品配料"""
    __tablename__ = "cp_dosing"

    id = Column(Integer, primary_key=True)  # 分类编号
    name = Column(String(200), nullable=False)  # 分类名
    num = Column(String(200), nullable=False)
    food_id = Column(Integer, ForeignKey("cp_foods.id"))


class Method(Base):
    """菜品做法"""
    __tablename__ = "cp_method_of_work"

    id = Column(Integer, primary_key=True)  # 分类编号
    titles = Column(String(200), nullable=False)  # 分类名
    default_image = Column(String(200), nullable=False)  # 分类名
    sequence = Column(Integer)
    food_id = Column(Integer, ForeignKey("cp_foods.id"))


head = {
    "Cookie": "__gads=ID=5145f9e5dea0fc90:T=1573355477:S=ALNI_MYfK0Cdh3YgqVZWPi0wDVxQJfM9bw; pid=17051573355500860; msc-user-sign-mark=1; uchome_loginuserid=12368776; uchome_loginuser=tnanpmOXM; Hm_lvt_fb9cd9dcdda23cee0c7357db9be24acb=1585018483,1585188115,1585218070,1585218117; PHPSESSID=pjge7m0e5ks3edlnb418vi35ns; Hm_lpvt_fb9cd9dcdda23cee0c7357db9be24acb=1585224873",
    "Host": "home.meishichina.com",
    "Pragma": "no-cache",
    "Sec-Fetch-Dest": "document",
    "Sec-Fetch-Mode": "navigate",
    "Sec-Fetch-Site": "none",
    "Sec-Fetch-User": "?1",
    "Upgrade-Insecure-Requests": "1",
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36"
}


# 主食， 家常菜， 川菜，家庭宴席, 颜值, 素菜，鲁菜,粤菜
class Fly:

    def __init__(self, num, name):
        self.cate = num
        self.reids = redis.StrictRedis(host='localhost', port=6379, db=3)
        # 创建userinfo表（所有表结构）
        # Base.metadata.create_all(engine)
        DBSession = sessionmaker(bind=engine)  # 创建与数据库的会话，返回的是一个类
        # 创建session对象
        self.session = DBSession()  # 生成链接数据库的实例
        self.get_url = "https://home.meishichina.com/recipe/%s/page/2/" % name

    def run(self):
        a = requests.get(self.get_url, headers=head)
        a.encoding = "utf-8"
        lists = etree.HTML(a.text)
        li_list = lists.xpath("//div[@id='J_list']/ul/li")
        for i in li_list:
            url = i.xpath("./div[@class='pic']/a/@href")
            self.connent(url[0])

    def connent(self, url):
        if self.reids.get(url):
            ids = int(self.reids.get(url))
            a = self.session.query(FoodCate).filter(FoodCate.category_id == self.cate, FoodCate.food_id == ids).all()
            if a:
                print("该分类的菜品也存在")
                return
            if ids == 1:
                ids = randint(2, 12)
            food_cate = FoodCate(category_id=self.cate, food_id=ids)
            self.session.add(food_cate)
            self.session.commit()
            print("该url已经被爬过了", url)
            return
        a = requests.get(url, headers=head)
        a.encoding = "utf-8"
        html = etree.HTML(a.text)
        name = html.xpath("//h1[@class='recipe_De_title']/a/text()")
        food_img = html.xpath("//a[@class='J_photo']/img/@src")
        food_title = html.xpath("//div[@class='block_txt1']/text()")

        peliao = html.xpath("//fieldset//ul/li")
        buzo = html.xpath("//div[@class='recipeStep']/ul/li")
        print(name, food_img, food_title)
        if not name:
            print(url)
            print(a.text)
        name = re.findall(u"[\u4e00-\u9fa5]+", name[0])
        names = ""
        for na in name:
            names += na
        food = CP_Foods(name=names, default_image=food_img[0], title=food_title[0] if food_title else name[0])

        self.session.add(food)
        self.session.commit()
        food_cate = FoodCate(food_id=food.id, category_id=self.cate)
        self.session.add(food_cate)
        self.session.commit()
        for i in peliao:
            dosing = DoSing()
            pl_name = i.xpath(".//b/text()")
            num = i.xpath("./span[@class='category_s2']/text()")
            dosing.name = pl_name[0]
            dosing.num = num[0]
            dosing.food_id = food.id
            self.session.add(dosing)
            self.session.commit()
        nums = 1
        for i in buzo:
            method = Method()
            title = i.xpath("./div[@class='recipeStep_word']/text()")
            img = i.xpath("./div[@class='recipeStep_img']/img/@src")
            method.food_id = food.id
            method.sequence = nums
            if not title:
                title = i.xpath("./div[@class='recipeStep_word noStep_word']/text()")
            # title[0] = re.sub(r"😋|😄|🥗", "", title[0])
            title = re.findall(u"[\u4e00-\u9fa5]+", title[0])
            # print('res=', res)
            # title[0] = re.sub(r"😋|😄|🥗", "", title[0])
            titles = ""
            for j in title:
                titles += j
            if len(titles) >= 200:
                titles = titles[:190]
            method.titles = titles
            if not img:
                img = i.xpath("./div[@class='recipeStep_img noStep_img']/img/@src")

            method.default_image = img[0] if img else ""
            nums += 1
            self.session.add(method)
            self.session.commit()
        self.reids.set(url, food.id)

    def __del__(self):
        self.session.close()
        # print("数据库已关闭")


# fly = Fly()
# fly.run()
xh = {
    "jianbiyangwei": 49
      }
for i, j in xh.items():
    fly = Fly(j, i)
    fly.run()
